/*
 * @(#)UpgradeHandler4SystemSetup.java
 *
 * Copyright (c) 2004 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.upgrade.handler;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.commons.dbutils.DbUtils;

import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.Utility;
import com.dcivision.upgrade.UpgradeContext;

/**
 * Class Implements UpgradeHandler is responsible to perform the upgrade of system setup part.
 * 
 * @author      Brook.Huang
 * @company     DCIVision Limited
 * @creation    19/04/2007
 * @version     $Revision: 1.1.2.1 $
 */
public class UpgradeHandler4SystemSetup319To320 extends UpgradeHandlerBase {
  
  public UpgradeHandler4SystemSetup319To320(UpgradeContext upgradeContext) throws Exception {
    super(upgradeContext);
  }

  /**
   * @see com.dcivision.upgrade.handler.UpgradeHandlerBase#upgradeBegin()
   */
  protected void upgradeBegin() throws Exception {
    upgradeContext.logReport("<p><b>START UPGRADE PARADM SYSTEM SETUP MODULE</b></p>\n", null);
  }

  /**
   * @see com.dcivision.upgrade.handler.UpgradeHandlerBase#upgradeEnd()
   */
  protected void upgradeEnd() throws Exception {
    processRolePermission();
  }
  
  /*
   * Setup role permission make every role have default permission to read message box in homepage.
   */
  private int processRolePermission() throws Exception {
    upgradeContext.logReport("<p><font color=blue>START UPGRADE USER_ROLE PERMISSION</font><br>\n", null);
    
    int count = 0;
    
    Statement roleListStat = upgradeContext.getUpgradeConnection().createStatement();
    ResultSet roleListRs = null;
    
    try {
      roleListRs = roleListStat.executeQuery(
          "SELECT " +
          "  ID, " +
          "  CREATOR_ID, " +
          "  CREATE_DATE, " +
          "  UPDATER_ID, " +
          "  UPDATE_DATE " +
          "FROM " +
          "  USER_ROLE " +
          "WHERE " +
          "  RECORD_STATUS = 'A'"
          );
      
      final int objectID = 9001; // 9001 is the message box's function code id.
      final String permissionSymbol = "R"; // Default permission is read.
      
      while (!Utility.isEmpty(roleListRs) && roleListRs.next()) {
        log.info("Looping to fetch all role ids from USER_ROLE...");
        count += insertUserRolePermission(roleListRs.getInt("ID"), objectID, permissionSymbol, roleListRs.getInt("CREATOR_ID"), roleListRs.getString("CREATE_DATE"),
            roleListRs.getInt("UPDATER_ID"), roleListRs.getString("UPDATE_DATE"));
      }
      
    } finally {
      DbUtils.closeQuietly(roleListStat);
      DbUtils.closeQuietly(roleListRs);
    }
    
    upgradeContext.logReport(
        "<font color=blue>UPGRADE USER_ROLE_PERMISSION SUCCESS (TOTAL UPDATE COUNT: " + count + ")</font><br></p>\n", null);
    
    return count;
  }
  
  private int insertUserRolePermission(final int USER_ROLE_ID, final int OBJECT_ID, final String PERMISSION, int CREATOR_ID, String CREATE_DATE, int UPDATER_ID,
      String UPDATE_DATE) throws Exception {
    int count = 0;

    PreparedStatement stat = null;
    StringBuffer sqlStat = new StringBuffer();

    try {
      sqlStat.append(
          "INSERT INTO " +
          "  USER_ROLE_PERMISSION " +
          "  (ID, USER_ROLE_ID, OBJECT_TYPE, OBJECT_ID, PERMISSION, MUST_FLAG, START_TIME, END_TIME, GRANTER_USER_RECORD_ID, CAN_GRANT, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE) " +
          "VALUES " +
          "  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
          );

      stat = upgradeContext.getUpgradeConnection().prepareStatement(sqlStat.toString());

      int queryIndex = 1;
      int ID = DataSourceFactory.getNextSequence("USER_ROLE_PERMISSION").intValue();

      stat.setInt(queryIndex++, ID);
      stat.setInt(queryIndex++, USER_ROLE_ID);
      stat.setString(queryIndex++, "F"); // System type.
      stat.setInt(queryIndex++, OBJECT_ID);
      stat.setString(queryIndex++, PERMISSION);
      stat.setString(queryIndex++, "N"); // Must flag default is fale.
      stat.setObject(queryIndex++, null);
      stat.setObject(queryIndex++, null);
      stat.setObject(queryIndex++, null);
      stat.setObject(queryIndex++, null);
      stat.setString(queryIndex++, "A"); // Active status.
      stat.setInt(queryIndex++, 0);
      stat.setInt(queryIndex++, CREATOR_ID);
      stat.setString(queryIndex++, CREATE_DATE);
      stat.setInt(queryIndex++, UPDATER_ID);
      stat.setString(queryIndex++, UPDATE_DATE);

      count = stat.executeUpdate();

      if (count > 0) {
        upgradeContext.logReport(null, "Insert(ID:    " + ID + ")    <br>\n");
      }
    } finally {
      DbUtils.closeQuietly(stat);
    }

    return (count);
  }

}
